package org.uim.cricket.storage.queries;

import java.util.Calendar;

import org.uim.cricket.entities.Player;
import org.uim.cricket.entities.match.CricketMatch;
import org.uim.cricket.entities.match.MatchOfficials;
import org.uim.cricket.entities.match.MatchResult;
import org.uim.cricket.entities.match.ResultWonType;
import org.uim.cricket.entities.match.Venue;

public class MatchQueryGenerator extends QueryGenerator {

	public MatchQueryGenerator(CricketMatch theMatch) {
		super(theMatch);
	}

	@Override
	public String generateQuery() throws Exception {
		CricketMatch match = this.getMatch();
		
		String id = match.getId().getPrefix() + "#" + match.getId().getInternationalID();
		
		StringBuilder query = new StringBuilder("CALL sp_Add_Match(");
		this.appendQuery(query, id, 12, true);
		this.appendQuery(query, match.getId().getPrefix(), 8, false);
		this.appendQuery(query, match.getTeam1(), 128, false);
		this.appendQuery(query, match.getTeam2(), 128, false);
		query.append(match.getGround().getId()).append(",");
		query.append(match.isDayNight()).append(",");
		query.append(match.getSeries().getId()).append(",");
		if (match.getDates() != null && match.getDates().getDates() != null) {
			Calendar[] cals = match.getDates().getDates();
			if (cals.length == 1) {
				query.append(this.getStrCalendar(cals[0])).append(",NULL,");
			} else {
				query.append(this.getStrCalendar(cals[0])).append(",");
				query.append(this.getStrCalendar(cals[cals.length - 1])).append(",");
			}
		} else {
			query.append("NULL,NULL,");
		}
		if (match.getVenue() == Venue.NEUTRAL) {
			query.append("true");
		} else {
			query.append("false");
		}
		query.append(");\n");
		
		if (match.getResult() != null) {
			MatchResult res = match.getResult();
			
			query.append("CALL sp_Add_Result(");
			this.appendQuery(query, id, 12, true);
			this.appendQuery(query, res.getResultType().toString(), 24, false);
			this.appendQuery(query, res.getWonTeam(), 128, true);
			if (res.getMargin() >= 0) 
				this.appendQuery(query, res.getMargin());
			else
				query.append("NULL,");
			
			query.append(res.isByInnings()).append(",");
			if (res.getWonType() == ResultWonType.BY_RUNS) query.append("'R',");
			else if (res.getWonType() == ResultWonType.BY_WICKETS) query.append("'B',");
			else if (res.getWonType() == ResultWonType.BY_DAFAULT) query.append("'D',");
			else query.append("NULL,");
			
			if (res.getRemainingBalls() >= 0)
				this.appendQuery(query, res.getRemainingBalls());
			else
				query.append("NULL,");
			
			query.append(res.isByDuckworthLewis()).append(");\n");
		}
		
		if (match.getOfficials() != null) {
			MatchOfficials offs = match.getOfficials();
			
			if (offs.getUmpires() != null && offs.getUmpires().length > 0) {
				for (int u=0;u<offs.getUmpires().length;u++) {
					query.append("CALL sp_Add_Match_Official(");
					this.appendQuery(query, id, 12, false);
					query.append(offs.getUmpires()[u].getId()).append(",");
					query.append("'Umpire');\n");
				}
			}
			
			if (offs.getReferee() != null) {
				query.append("CALL sp_Add_Match_Official(");
				this.appendQuery(query, id, 12, false);
				query.append(offs.getReferee().getId()).append(",");
				query.append("'Match Referee');\n");
			}
			
			if (offs.getReservedUmpire() != null) {
				query.append("CALL sp_Add_Match_Official(");
				this.appendQuery(query, id, 12, false);
				query.append(offs.getReservedUmpire().getId()).append(",");
				query.append("'Reserved Umpire');\n");
			}
			
			if (offs.getTvUmpire() != null) {
				query.append("CALL sp_Add_Match_Official(");
				this.appendQuery(query, id, 12, false);
				query.append(offs.getTvUmpire().getId()).append(",");
				query.append("'TV Umpire');\n");
			}
		}
		
		if (match.getManOfTheMatch() != null && match.getManOfTheMatch().length > 0) {
			
			for (int m=0; m<match.getManOfTheMatch().length; m++) {
				Player p = match.getManOfTheMatch()[m];
				if (p==null) continue;
				
				query.append("CALL sp_Add_ManOfTheMatch(");
				this.appendQuery(query, id, 12, false);
				query.append(p.getId()).append(");\n");
			}
			
		}
		
		if (match.getManOfTheSeries() != null && match.getManOfTheSeries().length > 0) {
			
			for (int m=0; m<match.getManOfTheSeries().length; m++) {
				Player p = match.getManOfTheSeries()[m];
				if (p==null) continue;
				
				query.append("CALL sp_Add_ManOfTheSeries(");
				query.append(match.getSeries().getId()).append(",");
				query.append(p.getId()).append(");\n");
			}
			
		}
		
		return query.toString();
	}
	
	private String getStrCalendar(Calendar cal) {
		if (cal == null) return "NULL";
		return "'" + cal.get(Calendar.YEAR) + "-" + (cal.get(Calendar.MONTH) + 1) + "-" 
			+ cal.get(Calendar.DATE) + "'";
	}

}
